const path = require('path');
const sqlite3 = require('sqlite3').verbose();
const fs = require('fs');
const os = require('os');
const {app} = require('electron');

const dbDir = path.join(os.homedir(), '.gkblocks', 'data'); // 跨平台支持
const dbPath = path.join(dbDir, 'fenbi.db');
if (!fs.existsSync(dbDir)) {
    fs.mkdirSync(dbDir, {recursive: true});
}

const db = new sqlite3.Database(dbPath);

// 更新ques表被打印次数
function updateHistoryCount(ids){
    return new Promise((resolve, reject) => {
        const placeholders = ids.map(() => '?').join(', ');
        const sql = `UPDATE ques SET history_count = history_count + 1 WHERE id IN (${placeholders})`;
        console.log("sql:", sql);
        db.run(sql, ids, (err) => {
            if (err) {
                console.error("更新失败:", err);
                reject(err.message);
            } else {
                console.log("更新成功");
                resolve(ids);
            }
        });
    })

}

// 插入收藏
function addStarItem(ids, name, answers) {
    return new Promise((resolve, reject) => {
        const idsStr = String(ids);
        const answersStr = String(answers);
        const now = new Date().toISOString();

        // 查重
        const selectSQL = `SELECT name
                           FROM star
                           WHERE ques_ids = ?
                              OR name = ?
                           LIMIT 1`;
        db.get(selectSQL, [idsStr, name], (err, row) => {
            if (err) {
                console.error('查重失败：', err);
                reject(err);
                return;
            } else if (row) {
                if (row.name === name) {
                    reject(`发现重复的试卷命名：${row.name}`);
                } else {
                    reject(`该试卷已被收藏过，试卷名：${row.name}`);
                }
                return;
            }

            const sql = `INSERT INTO star (ques_ids, name, change_at, ques_answers)
                         VALUES (?, ?, ?, ?)`;
            db.run(sql, [idsStr, name, now, answersStr], (err) => {
                if (err) {
                    console.error('插入star失败：', err);
                    reject(err);
                } else {
                    resolve({id: this.lastID});
                }
            });
        });
    });
}

// 移除收藏
function removeStarItem(id) {
    return new Promise((resolve, reject) => {
        const sql = `DELETE
                     FROM star
                     WHERE id = ?`;
        db.run(sql, [id], (err) => {
            if (err) {
                console.error(err);
                reject(err);
            } else {
                // this.changes 表示影响的行数
                if (this.changes === 0) {
                    reject('数据异常：未找到对应记录');
                } else {
                    resolve();
                }
            }
        });
    });
}

// 分页查询收藏列表
function getStarList(limit, offset) {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `SELECT *
                   FROM star`;

        sql += ` ORDER BY change_at DESC LIMIT ? OFFSET ?`;
        params.push(limit, offset);

        db.all(sql, params, (err, rows) => {
                if (err) {
                    console.error('分页查询失败：', err);
                    reject(err);
                } else {
                    resolve(rows);
                }
            }
        );
    });
}

// 获取收藏总数
function getStarCount() {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `SELECT COUNT(*) AS count
                   FROM star`;

        db.get(sql, params, (err, row) => {
                if (err) {
                    console.error('获取总数失败：', err);
                    reject(err);
                } else {
                    resolve(row.count);
                }
            }
        );
    });
}

// 分页获取打印记录
function getHistoryList(limit, offset) {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `SELECT *
                   FROM history`;

        sql += ` ORDER BY change_at DESC LIMIT ? OFFSET ?`;
        params.push(limit, offset);

        db.all(sql, params, (err, rows) => {
                if (err) {
                    console.error('分页查询失败：', err);
                    reject(err);
                } else {
                    resolve(rows);
                }
            }
        );
    });
}

// 获取打印记录总数
function getHistoryCount() {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `SELECT COUNT(*) AS count
                   FROM history`;

        db.get(sql, params, (err, row) => {
                if (err) {
                    console.error('获取总数失败：', err);
                    reject(err);
                } else {
                    resolve(row.count);
                }
            }
        );
    });
}

// 插入打印记录
function insertIntoHistory(ids, name, answers) {
    return new Promise((resolve, reject) => {
        const idsStr = String(ids);
        const answersStr = String(answers);
        const now = new Date().toISOString();

        const sql = `INSERT INTO history (ques_ids, name, change_at, ques_answers)
                     VALUES (?, ?, ?, ?)`;
        db.run(sql, [idsStr, name, now, answersStr], function (err) {
            if (err) {
                console.error('插入history失败：', err);
                reject(err);
            } else {
                // 返回自增主键
                resolve({id: this.lastID});
            }
        });
    });
}

// 分页获取考题
function getQuesListWithPagination(limit, offset, type, rating, sortByDiff, sortByCount) {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `SELECT *
                   FROM ques
                   WHERE 1 = 1`;

        if (type && type.trim() !== '') {
            sql += ` AND ques_type = ?`;
            params.push(type.trim());
        }

        if (rating && rating.trim() !== '') {
            const match = rating.match(/(\d+)\s*-\s*(\d+)/);
            if (match) {
                const min = parseInt(match[1], 10);
                const max = parseInt(match[2], 10);
                sql += ' AND rating BETWEEN ? AND ?';
                params.push(min, max);
            }
        }
        let orderBy = [];
        if (sortByCount) orderBy.push("history_count DESC");
        if (sortByDiff) orderBy.push("rating ASC");
        orderBy.push("id ASC");
        sql += ` ORDER BY ${orderBy.join(', ')}`;

        sql += ` LIMIT ? OFFSET ? `;

        params.push(limit, offset);

        console.log(`sql : ${sql}`);
        console.log(`params: ${JSON.stringify(params)}`);

        db.all(sql, params, (err, rows) => {
                if (err) {
                    console.error('分页查询失败：', err);
                    reject(err);
                } else {
                    resolve(rows);
                }
            }
        );
    });
}

// 获取考题总数
function getQuesCount(type, rating) {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `
            SELECT COUNT(*) AS count FROM ques WHERE 1 = 1`;

        if (type && type.trim() !== '') {
            sql += ` AND ques_type = ? `;
            params.push(type.trim());
        }

        if (rating && rating.trim() !== '') {
            const match = rating.match(/(\d+)\s*-\s*(\d+)/);
            if (match) {
                const min = parseFloat(match[1]);
                const max = parseFloat(match[2]);
                sql += ' AND rating BETWEEN ? AND ?';
                params.push(min, max);
            }
        }

        console.log(`sql:${sql}`);
        console.log(`params: ${JSON.stringify(params)}`);

        db.get(sql, params, (err, row) => {
                if (err) {
                    console.error('获取总数失败：', err);
                    reject(err);
                } else {
                    resolve(row.count);
                }
            }
        );
    });
}

// 按id列表获取考题，结果同列表顺序一致
function getQuesListByIds(ids) {
    return new Promise((resolve, reject) => {
        if (!ids.length) return resolve([]); // 防止空数组报错

        const placeholders = ids.map(() => '?').join(','); // 动态拼接占位符
        const orderCase = ids
            .map((id, index) => `WHEN ? THEN ${index}`)
            .join(' ');
        const sql = `
            SELECT *
            FROM ques
            WHERE id
            IN (${placeholders})
            ORDER BY CASE
            id ${ orderCase}
        END
        `;
        const params = [...ids, ...ids]; // 传两遍参，一遍用于 IN，一遍用于 ORDER BY CASE

        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('查询失败：', err);
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
}

// 获取考题类型及相应的题量
function getQuesTypes(rating) {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `
            SELECT ques_type AS type, COUNT(* ) AS count
            FROM ques
            WHERE 1 = 1
        `;
        if (rating) {
            // 匹配括号内的数字范围，如 "高（0-40%）" 提取 0 和 40
            const match = rating.match(/(\d+)\s*-\s*(\d+)/);
            if (match) {
                const min = parseFloat(match[1]);
                const max = parseFloat(match[2]);
                sql += ' AND rating BETWEEN ? AND ?';
                params.push(min, max);
            }
        }
        sql += ' GROUP BY type ORDER BY count DESC';

        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('查询失败：', err);
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
}

// 获取考题难度分级及相应题量
function getQuesRatingRanges(type) {
    return new Promise((resolve, reject) => {
        const params = [];
        let whereClause = "WHERE rating IS NOT NULL AND rating != ''";
        if (type && type !== '') {
            whereClause += " AND ques_type = ?";
            params.push(type);
        }
        const sql = `
            SELECT CASE
                       WHEN
                           CAST(REPLACE(rating, '%', '')
                               AS
                               REAL
                           ) <
                           40
                           THEN
                           '高（0-40%）'
                       WHEN
                           CAST(REPLACE(rating, '%', '')
                               AS
                               REAL
                           ) <
                           70
                           THEN
                           '中（40-70%）'
                       ELSE
                           '低（70-100%）'
                       END
                       AS
                       difficulty,
                   COUNT(*) AS count
            FROM ques ${whereClause}
            GROUP BY
                difficulty
            ORDER BY
                difficulty
        `;

        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('查询失败：', err);
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
}

/**
 * 按照指定条件获取随机考题
 * @param count 需要的考题数
 * @param type ？考题类型
 * @param rating ？ 考题难度区间，字符串类型（"0-30","31-70","71-100")
 * @param exclude ？ 需要排除的考题，number数组
 * 返回符合条件的考题id列表
 */
function getRandomIdList(count, type, rating, exclude) {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `
            SELECT id
            FROM ques
            WHERE 1 = 1`;
        if (type) {
            sql += `
        AND
        ques_type = ? `;
            params.push(type);
        }
        if (rating) {
            const match = rating.match(/(\d+)\s*-\s*(\d+)/);
            if (match) {
                const min = parseFloat(match[1]);
                const max = parseFloat(match[2]);
                sql += ' AND rating BETWEEN ? AND ?';
                params.push(min, max);
            }
        }
        if (exclude && exclude.length) {
            sql += ` AND id NOT IN(${exclude.map(() => '?').join(',')})`;
            params.push(...exclude);
        }
        sql += `
        ORDER
        BY
        RANDOM()
        LIMIT ? `;
        params.push(count);
        console.log(`sql:${sql}`);
        console.log(`params: ${JSON.stringify(params)}`);

        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('查询失败：', err);
                reject(err);
            } else {
                resolve(rows.map(row => row.id));
            }
        });
    })
}

module.exports = {
    getQuesListWithPagination,
    getQuesCount,
    getQuesListByIds,
    getQuesTypes,
    getQuesRatingRanges,
    getRandomIdList,
    insertIntoHistory,
    getHistoryCount,
    getHistoryList,
    getStarCount,
    getStarList,
    removeStarItem,
    addStarItem,
    updateHistoryCount
};
